<?php


namespace App\Admin\Extensions;

use DateTime;
use Dcat\Admin\Grid\Exporters\AbstractExporter;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use Illuminate\Support\Arr;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class StatistcsExcelExpoter extends AbstractExporter
{


    public function export()
    {


        $data = collect($this->buildData(1, 10000))->map(function ($item) {
            return Arr::only($item, [   'engineering',
                                        'time',
                                        'type',
                                        'num'
                                    ]);
        });

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        //设置sheet的名字  两种方法
        $sheet->setTitle('sheet1');
//        $spreadsheet->getActiveSheet()->setTitle('Hello');
        //设置第一行小标题
        $k = 1;
        $sheet->setCellValue('A' . $k, '所属工程');
        $sheet->setCellValue('B' . $k, '时间');
        $sheet->setCellValue('C' . $k, '行为');
        $sheet->setCellValue('D' . $k, '人数');

        // 设置个表格宽度
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);

        // 垂直居中
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);


        $info = $data;
        //  设置A单元格的宽度 同理设置每个
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        //  设置第三行的高度
        $spreadsheet->getActiveSheet()->getRowDimension('3')->setRowHeight(50);
        //  A1水平居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        //  将A3到D4合并成一个单元格
        $spreadsheet->getActiveSheet()->mergeCells('A3:D4');
        //  拆分合并单元格
        $spreadsheet->getActiveSheet()->unmergeCells('A3:D4');
        //  将A2到D8表格边框 改变为红色
        $styleArray = [
            'borders' => [
                'outline' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
                    'color' => ['argb' => 'FFFF0000'],
                ],
            ],
        ];
        //  $sheet->getStyle('A2:E8')->applyFromArray($styleArray);
        //  设置超链接
        //  $sheet->setCellValue('D6', 'www.baidu.com');
        //  $spreadsheet->getActiveSheet()->setCellValue('E6', 'www.baidu.com');
        //  循环赋值

        $k = 2;
        foreach ($info as $key => $value) {
            if($value['type'] == 1){$type = '入职';}
            if($value['type'] == 2){$type = '离职';}
            if($value['type'] == 3){$type = '再入职';}
            if($value['type'] == 4){$type = '劣迹';}
            $sheet->setCellValueExplicit('A' . $k, $value['engineering'], 'str');
            $sheet->setCellValueExplicit('B' . $k, $value['time'],'str');
            $sheet->setCellValueExplicit('C' . $k, $type, 'str');
            $sheet->setCellValueExplicit('D' . $k, $value['num'], 'str');
            $sheet->getRowDimension($k)->setRowHeight(80);
            $k++;
        }

        $engineering = $_GET['engineering'];
        $job_num= DB::table('work_user')->where('engineering',$engineering)
            ->where('status',1)->where('misdeed',1)
            ->count();
        $resign_num = DB::table('work_user')->where('engineering',$engineering)
            ->where('status',2)->where('misdeed',1)
            ->count();
        $misdeed_num = DB::table('work_user')->where('engineering',$engineering)
            ->where('misdeed','<>',1)
            ->count();


        $sheet->setCellValueExplicit('A' . $k, '在职总人数:'.$job_num, 'str');
        $sheet->setCellValueExplicit('B' . $k, '离职总人数:'.$resign_num, 'str');
        $sheet->setCellValueExplicit('C' . $k, '劣迹总人数:'.$misdeed_num, 'str');

        $file_name = date('Y-m-d', time()) . rand(1000, 9999);


        //保存的路径可自行设置
//        $file_name = '../'.$file_name . ".xlsx";
//        $writer->save($file_name);
        //  第二种直接页面上显示下载
        $file_name = $file_name . ".xlsx";
        header('Content-Type: application/vnd.ms-excel');
        header('Expires: 0');
        header('Pragma: public');
        header('Content-Disposition: attachment;filename="' . $file_name . '"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        //  注意createWriter($spreadsheet, 'Xls') 第二个参数首字母必须大写
        ob_end_clean();
        $writer->save('php://output');
        exit();
    }

    public function getClient(){
        $client = new Client();
        return $client;
    }

    public static function curlGet($url)
    {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_HEADER, 0);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); // 这个是重点 请求https。
        $data = curl_exec($ch);
        curl_close($ch);
        return $data;
    }


}
